
  global data Z:\STATA files
  global outputDS Z:\Figures\Descriptive
  global export Z:\Figures
  
  ******************************************************************
  * additional analysis appendix D4 (raw datasets created in Fig6.do)
  ****************************************************************
  
   use "$data\panelmeldklant.dta", clear 
   merge m:1 year Werknemer_ID using "$data\panelpersoon.dta"
   drop _m 
   rename nationaliteit nationality_worker
   rename Geslacht gender_worker
   rename leeftijdsklasse age_worker
   rename nationaliteit_klasse nationality_worker_kl
   merge m:1 year Werkgever_ID using "$data\panelwerkgev.dta"
   drop if _m==1
   drop _m
   rename Adres_land country_employer
   rename Adres_land_klasse country_employer_kl
    
   
   bys year: count 
   duplicates tag Begindatum_act Einddatum_act Werknemer_ID Werkgever_ID year Klant_ID, g(flag2)
	   
    tab flag2
	   
	egen ID1=group( Begindatum_act Einddatum_act Werknemer_ID Werkgever_ID year Klant_ID)   
	   
	   sort ID1
	   bys ID1: keep if _n==_N
	   drop ID1
	   
   duplicates tag Begindatum_act Einddatum_act Werknemer_ID Werkgever_ID year, g(flag)
   sort Begindatum_act Einddatum_act Werknemer_ID Werkgever_ID year
   
   g imp=1
   egen ID=group( Werknemer_ID Werkgever_ID Begindatum_act Einddatum_act year)
   sort Werknemer_ID Werkgever_ID Begindatum_act Einddatum_act year
   bys  ID : gen cum=sum(imp)
   
   drop flag2
   drop flag 
   
   replace Klant_ID=99999 if Klant_ID==4153134529
   reshape wide Klant_ID, i(Werknemer_ID Werkgever_ID Begindatum_act Einddatum_act year) j(cum)
   save "$data\panelmeldklantreshaped.dta", replace






    use "$data\panelmeldpow.dta", clear
    bys year: count 
    duplicates tag Begindatum_act Einddatum_act Werknemer_ID Werkgever_ID    Detacheringsplaats_ID year, g(flag2) 
	egen ID1=group(Begindatum_act Einddatum_act Werknemer_ID Werkgever_ID    Detacheringsplaats_ID year)   
	   
	 sort ID1
	 bys ID1: keep if _n==_N
	 drop ID1
 merge m:1 Werknemer_ID Werkgever_ID Begindatum_act Einddatum_act year using "$data\panelmeldklantreshaped.dta"
  tab _m 
  g indicmatch="." 
  replace indicmatch="POW and ML" if _m==3 
  replace indicmatch="POW ONLY" if _m==1
  replace indicmatch="KL ONLY" if _m==2
  drop _m
  merge m:1 Detacheringsplaats_ID year using "$data\paneldetach.dta"
  *6,000 unkown 
  replace Detacheringsplaats_ID=99999  if Detacheringsplaats_ID==88123191
  drop if _m==2 
  drop if _m==1
  rename gecodeerd_KBO KBO_POW
  drop  Klant_ID6-Klant_ID56
  reshape long Klant_ID, i(Werknemer_ID Werkgever_ID Begindatum_act Einddatum_act year Detacheringsplaats_ID) j(cum)
  drop if missing(Klant_ID)
  save "$data/paneldetachement_full.dta", replace
  
  
   use "$data/paneldetachement_full.dta", clear
   egen u_KlantID=group(Klant_ID)
   egen u_worker=group(Werknemer_ID)
   egen u_sendingfirm=group(Werkgever_ID)
   sum u_KlantID u_worker u_sendingfirm
   drop _m 
   replace  Klant_ID=4153134529 if Klant_ID==99999
   merge m:1 Klant_ID using "$data\pse_1_pop_entreprises_code.dta"
   save "$data/paneldetachement_full_final.dta", replace
   
   

use "$data/paneldetachement_full_final.dta", clear
g sector_mission="." 
replace sector_mission="Agriculture" if Sector_ID==23000030
replace sector_mission="Mining and Quarrying" if Sector_ID==23000031
replace sector_mission="Manufacture of Metals and Machines" if Sector_ID==23000032
replace sector_mission="Manufacture of Chemicals" if Sector_ID==23000033
replace sector_mission="Manufacture of Food Products" if Sector_ID==23000034
replace sector_mission="Manufacture of Textiles" if Sector_ID==23000035
replace sector_mission="Woodwork" if Sector_ID==23000036
replace sector_mission="Cleaning" if Sector_ID==23000037
replace sector_mission="Electricity, Gas and Water" if Sector_ID==23000038
replace sector_mission="Construction" if Sector_ID==23000039
replace sector_mission="Wholesale" if Sector_ID==23000040
replace sector_mission="Market Vendors" if Sector_ID==23000041
replace sector_mission="Hotels and Catering" if Sector_ID==23000042
replace sector_mission="Transport" if Sector_ID==23000043
replace sector_mission="Finance and Insurance" if Sector_ID==23000044
replace sector_mission="Real Estate" if Sector_ID==23000045
replace sector_mission="Consultancy" if Sector_ID==23000046
replace sector_mission="Engineering" if Sector_ID==23000047
replace sector_mission="ICT" if Sector_ID==23000048
replace sector_mission="Education" if Sector_ID==23000049
replace sector_mission="Health Care" if Sector_ID==23000050
replace sector_mission="Culture" if Sector_ID==23000051
replace sector_mission="Domestic Work" if Sector_ID==23000052
replace sector_mission="Off Shore Activities" if Sector_ID==23000053
replace sector_mission="Art and Showbizz" if Sector_ID==23000054
replace sector_mission="Manufacture of Foods" if Sector_ID==23000055
replace sector_mission="Trade of Foods" if Sector_ID==23000056
replace sector_mission="Security" if Sector_ID==23000057
replace sector_mission="Electricity Installation" if Sector_ID==23000058
replace sector_mission="Others" if Sector_ID==23000999
replace sector_mission="ICT" if Sector_ID==23001006
replace sector_mission="Finance and Insurance" if Sector_ID==23001007
replace sector_mission="Manufacture of Metals and Machines" if Sector_ID==23001012
replace sector_mission="Trade in Goods" if Sector_ID==23001017
replace sector_mission="Others" if Sector_ID==23001999
replace sector_mission="Construction" if Sector_ID==23001001
replace sector_mission="Manufacture of Food Products" if Sector_ID==23001002
replace sector_mission="Agriculture" if Sector_ID==23001003
replace sector_mission="Cleaning" if Sector_ID==23001004
replace sector_mission="Security" if Sector_ID==23001005
replace sector_mission="Electricity, Gas and Water" if Sector_ID==23001008
replace sector_mission="Health Care" if Sector_ID==23001009
replace sector_mission="Electricity Installation" if Sector_ID==23001013
replace sector_mission="Manufacture of Chemicals" if Sector_ID==23001014
replace sector_mission="Transport" if Sector_ID==23001015
replace sector_mission="Hotels and Catering" if Sector_ID==23001018
replace sector_mission="Domestic Work" if Sector_ID==23001019
replace sector_mission="Woodwork" if Sector_ID==23001016
g freq=1 
collapse (sum) freq, by(Klant_ID sector_mission year Klant_ID gecodeerd_KBO)
egen y=min(year), by(Klant_ID)	
egen f=max(freq), by(Klant_ID year)
keep if year==y & freq==f
duplicates drop Klant_ID, force
keep Klant_ID year y f sector_mission
save "$data/sectorfirst.dta",replace


use "$data/baselinedataset.dta", clear 
drop _m
merge m:1 Klant_ID year using "$data/sectorfirst.dta"
drop if _m==2
encode(sector_mission), g(sectid)
egen sectix=min(sectid), by(gecodeerd_KBO)


tostring codnac3, g(nac) force
gen one=substr(nac,1,2)
gen oni=substr(nac,1,1)

reghdfe logemp coeff_bottom coeff7-coeff9 zero coeff11-coeff15 coeff_up if treated==1 & event_d>2013 & sectix==4 & oni!="4", absorb(year##codnac3 gecodeerd_KBO) cl(cluster)
est store others
reghdfe logemp coeff_bottom coeff7-coeff9 zero coeff11-coeff15 coeff_up if treated==1 & event_d>2013 & sectix==4 & oni=="4", absorb(year##codnac3 gecodeerd_KBO) cl(cluster)
est store construction


coefplot construction others, keep(coeff7 coeff8 coeff9 zero coeff11 coeff12 coeff13 coeff14 coeff15  ) omit vertical graphregion(  fcolor(white) lcolor(white)) ytitle("Log Total Employment") xtitle("Year Since First Posting Use") ///
	ylabel(-0.2 (0.1) 0.2) xline(4.5, lcolor(red)) ciopts(recast(rcap)) lwidth(medthick)
	
	g indicbig=0
	replace indicbig=1 if oni=="6" 
	replace indicbig=1 if oni=="7" 
	replace indicbig=1 if oni=="8" 
	replace indicbig=1 if oni=="9" 
	
	g construction=0
	replace construction=1 if codnac3>410 & codnac3<450
	
	g industrie=0
	replace industrie=1 if oni=="3"
	replace industrie=1 if oni=="2"
	replace industrie=1 if oni=="1"

	reghdfe logemp coeff_bottom coeff7-coeff9 zero coeff11-coeff15 coeff_up if treated==1 & event_d>2013 & sectix==4 & construction==1 , absorb(year##codnac3 gecodeerd_KBO) cl(cluster)
	est store construction
	coefplot construction, keep(coeff7 coeff8 coeff9 zero coeff11 coeff12 coeff13 coeff14 coeff15  ) omit vertical graphregion(  fcolor(white) lcolor(white)) ytitle("Log Total Employment") xtitle("Year Since First Posting Use") ///
	ylabel(-0.2 (0.1) 0.2) xline(4.5, lcolor(red)) ciopts(recast(rcap)) lwidth(medthick) yline(0, lcolor(gs10) lpattern(dash)) ///
	subtitle("Construction Service Outsourced by Using Firm in Construction Sector")
		graph export "Z:\Figures\logemp_ot_construction.png", replace
		
		
		
	reghdfe logemp coeff_bottom coeff7-coeff9 zero coeff11-coeff15 coeff_up if treated==1 & event_d>2013 & sectix==4 & industrie==1 , absorb(year##codnac3 gecodeerd_KBO) cl(cluster)
	est store construction
	coefplot construction, keep(coeff7 coeff8 coeff9 zero coeff11 coeff12 coeff13 coeff14 coeff15  ) omit vertical graphregion(  fcolor(white) lcolor(white)) ytitle("Log Total Employment") xtitle("Year Since First Posting Use") ///
	ylabel(-0.2 (0.1) 0.2) xline(4.5, lcolor(red)) ciopts(recast(rcap)) lwidth(medthick) yline(0, lcolor(gs10) lpattern(dash)) ///
	subtitle("Construction Service Outsourced by Using Firm in Manufacturing")
		graph export "Z:\Figures\logemp_ot_manufacturing.png", replace

		